from fastapi import Depends, FastAPI, BackgroundTasks, Form
from sqlalchemy.orm import Session
from sqlalchemy import func
from enum import Enum
from typing import List
import datetime
import curd
import models
import schemas
import trans_time
from databases import SessionLocal, engine
from starlette.requests import Request
from starlette.templating import Jinja2Templates
from starlette.staticfiles import StaticFiles
import pandas as pd

models.Base.metadata.create_all(bind=engine)
app = FastAPI()
templates = Jinja2Templates(directory="templates")
app.mount('/static', StaticFiles(directory='static'), name='static')


# 获取数据库
def get_db():
    try:
        db = SessionLocal()
        yield db
    finally:
        db.close()
        # print('数据库已关闭')


class LineName(str, Enum):
    w1 = 'w1'
    w2 = 'w2'
    w4 = 'w4'
    w5 = 'w5'
    w6 = 'w6'
    w7 = 'w7'
    w9 = 'w9'
    w10 = 'w10'
    r290 = 'r290'


@app.get("/analyse/")
async def analyse(db: Session = Depends(get_db)):
    interval = 100
    txt = curd.analyse(db=db, interval=interval)
    return txt


@app.get("/status/")
async def status(line: str, network: bool, PLC: bool, db_interval: int, startby: int, background_tasks: BackgroundTasks,
                 db: Session = Depends(get_db)):
    background_tasks.add_task(curd.status, db=db, line=line, PLC=PLC, network=network, db_interval=db_interval,
                              startby=startby)
    text = "{0}线最后一条记录跟现在之间相差{1}秒".format(line, db_interval)
    return text


@app.get("/status/{line}/")
async def line_status(line: str, db: Session = Depends(get_db)):
    return curd.get_status(db=db, line=line)


@app.post("/upload/")
async def upload(data: List[schemas.Upload], background_tasks: BackgroundTasks, db: Session = Depends(get_db)):
    background_tasks.add_task(curd.storage, db=db, data=data)
    return "已接收%s条数据。" % len(data)


@app.get("/data/")
async def getdata(end: str, start: str, line: str = None,
                  db: Session = Depends(get_db)):
    data = curd.getdata(db=db, start=start, end=end, line=line)
    return data


@app.get("/data/{line}/")
async def get_all_data(line: str, db: Session = Depends(get_db)):
    all_data = {}
    now = datetime.datetime.now()
    if now.hour > 20:
        endtime = datetime.datetime(now.year, now.month, now.day, 20, 0, 0)
    else:
        endtime = datetime.datetime(now.year, now.month, now.day, 8, 0, 0)
    starttime = endtime - datetime.timedelta(hours=12)
    while True:
        end = trans_time.datetime_toString(endtime)
        start = trans_time.datetime_toString(starttime)
        data = curd.getdata(db=db, start=start, end=end, line=line)
        if endtime.hour == 20:
            key = '{}月{}日白班'.format(endtime.month, endtime.day)
        elif endtime.hour == 8:
            key = '{}月{}日夜班'.format(starttime.month, starttime.day)
        all_data.update({key: data})
        endtime = starttime
        starttime = endtime - datetime.timedelta(hours=12)
        if starttime.month < now.month:
            break
    return all_data


# @app.post("/user/")
# 增加用户的功能暂时不用
async def add_user(user_ID: int, factory: str, name: str, pinyin: str, email: str, tel: str,
                   db: Session = Depends(get_db)):
    curd.add_user(db=db, user_ID=user_ID, factory=factory, name=name, pinyin=pinyin, email=email, tel=tel,
                  isActive=True)
    return "已增加用户%s~~" % name


@app.post("/tools/notice_num/")
async def get_notice_number(title: str = Form(...), user_id: int = 20105499, db: Session = Depends(get_db)):
    text = curd.get_notice_num(db=db, user_id=user_id, title=title)
    return text


@app.get("/tools/notice/")
async def notice_index(user_id: int, request: Request):
    return templates.TemplateResponse('notice.html', {'request': request, 'user_id': user_id})


@app.get("/tools/")
async def control_pan(user_id: int, request: Request, db: Session = Depends(get_db)):
    name, factory = curd.get_user_info(db=db, user_id=user_id)
    username = name[0] + "工"
    return templates.TemplateResponse('index.html', {'request': request, 'username': username, 'user_id': user_id})


# 以下是生产看板大屏的程序
@app.get("/")
async def show_line(line: str, request: Request):
    '''
    看板入口，带参数line，分别是w1-w10几条线的名字
    :param line: w1-w10，含r290
    :param request:
    :return: 返回main.html渲染模板
    '''
    if line in ['w4', 'w5', 'w6', 'w7']:
        return templates.TemplateResponse('index.html', {'request': request, 'line': line})
    else:
        return templates.TemplateResponse('index-cell.html', {'request': request, 'line': line})


@app.get("/time")
# 获得服务器时间入口
async def get_time():
    return curd.get_time()


@app.get("/top5/{line}")
# 右侧第一个图表的入口（急停点TOP5柱形图）
async def get_r1_data(line: str, db: Session = Depends(get_db)):
    data = curd.get_pause_data(db, line=line)
    print(data)
    if len(data) >= 5:
        mydata = data[0:5]
    else:
        mydata = data
    point = []
    times = []
    if data:
        for item in mydata:
            point.append(item[0])
            times.append(int(item[2]))
        return {"point": point, "times": times}


@app.get("/top5_total/{line}")
# 右侧第二个图表的入口（急停点饼图）
async def get_r2_data(line: str, db: Session = Depends(get_db)):
    data = curd.get_pause_data(db, line=line, workday=True)
    if len(data) >= 5:
        mydata = data[0:5]
    else:
        mydata = data
    values = []
    if data:
        for item in mydata:
            values.append({"value": int(item[2]), "name": item[0]})
        print(values)
        return values


@app.get("/output/{line}")
# 中间产量数据的入口
async def get_output(line: str, db: Session = Depends(get_db)):
    data_20 = curd.get_count(db, line=line, point="产量", interval=20)
    data_60 = curd.get_count(db, line=line, point="产量", interval=60)
    data_total = curd.get_count(db, line=line, point="产量", workday=True)
    now = datetime.datetime.now()
    if now.hour >= 8 and now.hour < 20:
        time = (now - datetime.datetime(now.year,
                                        now.month, now.day, 8, 0, 0)).seconds
    else:
        time = (now - datetime.datetime(now.year,
                                        now.month, now.day, 20, 0, 0)).seconds
    return {'plan': "2500", 'total': data_total, 'hour': data_60, 'ten_min': data_20,
            'time': int(time / (12 * 60 * 60) * 100)}


@app.get("/pass/{line}")
# 左边第二个商检直通数据的入口
async def get_pass_data(line: str, db: Session = Depends(get_db)):
    '''
    获取左边第二个商检直通数据的入口
    :param line: 线体名称
    :param db: 数据库
    :return: 返回三个值，（20分钟直通数量，1个小时直通数量，整个班直通数量）
    '''
    points = {'w7': ['直通'], 'w6': ['直通'], 'w5': ['直通'], 'w4': ['直通'], }
    '''
    定义一个字典，用于计算每条线商检房的直通数量、空车数量
    NO.1 “直通”，用于计算直通数量
    '''
    # 计算20分钟直通数量
    per_20 = curd.get_count(db, line, points[line][0], 20)
    print("20分钟直通数量%s" % per_20)
    # 计算60分钟直通数量
    per_60 = curd.get_count(db, line, points[line][0], 60)
    print("1个小时直通数量%s" % per_60)
    # 计算整个班直通数量
    total = curd.get_count(db, line, points[line][0], workday=True)
    print("整个班直通数量%s" % total)
    return {'total': total, 'hour': per_60, 'twenty_min': per_20}


@app.get("/beat/{line}")
async def get_l1_data(line: str, db: Session = Depends(get_db)):
    '''
    获取左边第一个节拍数据的入口
    :param line: 线体名称
    :param db: 数据库
    :return:各个关键点平均节拍的字典
    '''
    # data = curd.get_line_beat(db=db,line=line,workday=True)
    data = curd.get_line_beat(db=db, line=line, workday=True)
    keys = []
    values = []
    for key, value in data.items():
        keys.append(key)
        values.append(value)
    return {'key': keys, 'values': values}


@app.get("/beat_every/{line}")
async def get_l2_data(line: str, point: str = "产量", interval: int = 60, db: Session = Depends(get_db)):
    '''
    获得左边第二个每小时节拍数据的入口
    '''
    time_dict = {
        "day": ['8:00', '9:00', '10:00', '11:00', '12:00', '13:00', '14:00', '15:00', '16:00', '17:00', '18:00',
                '19:00', '20:00'],
        "night": ['20:00', '21:00', '22:00', '23:00', '0:00', '1:00', '2:00', '3:00', '4:00', '5:00', '6:00', '7:00',
                  '8:00']
    }
    beat_datas = []
    now = datetime.datetime.now()
    today = trans_time.datetime_toString(now)
    today = today.split()[0]
    print("today is : ", today)
    # 分白夜班
    if now.hour >= 8 and now.hour < 20:
        time_list = time_dict['day']
    else:
        time_list = time_dict['night']
    for i in range(len(time_list) - 1):
        if int(time_list[i].split(":")[0]) >= 8:
            today = trans_time.datetime_toString(now)
            today = today.split()[0]
        else:
            today = trans_time.datetime_toString(now + datetime.timedelta(1))
            today = today.split()[0]
        start = today + " " + time_list[i] + ":00"
        end = today + " " + time_list[i + 1] + ":00"
        print("strat is {0} and end is {1}".format(start, end))
        data = db.query(func.count(models.Pause.VarName)).filter(
            models.Pause.line == line,
            models.Pause.VarName.like("%{0}%".format(point)),
            models.Pause.DateTime.between(start, end)).first()
        beat_datas.append(data[0])
    return time_list, beat_datas


@app.get("/right_bottom/{line}")
def get_right_bottom(line: str, interval: int = 120):
    return int(curd.get_longtime(line, interval) / 60)


@app.get("/echarts_data/{line}")
def get_echarts_data(line: str, interval: int = 20, db: Session = Depends(get_db)):
    '''
    本函数用于获取echarts的数据
    line: 线体的名称
    interval: 时间间隔
    '''
    # 获得时间坐标轴
    time_list = get_time_list()
    # 定义需要替换的文字（为了让X轴坐标文字更简洁）
    replace_words = ["光电", "环形", "急停"]
    # 获得一个时间段的数据
    start, end = begin_by(interval)
    sql_interval = "select * from pause where DateTime between '{0}' AND '{1}' AND Line Like '{2}';".format(
        start, end, line)
    df_interval = pd.read_sql_query(sql_interval, engine)
    # 以下是计算节拍的时间
    beat = curd.get_beat(df=df_interval, keyword="节拍",
                         replace_words=replace_words)
    # 以下是计算产量节拍延时超过120秒的总时间
    start, end = begin_by()
    sql = "select * from pause where DateTime between '{0}' AND '{1}' AND Line Like '{2}';".format(
        start, end, line)
    df = pd.read_sql_query(sql, engine)
    long_time = curd.get_beat(
        df=df, keyword="产量", replace_words=replace_words, method="sum", thresholds=120)
    # 以下获得"直通"的数量，时间间隔是60分钟
    if line in ['w4','w5','w6','w7']:
        pass_num = curd.count_df(df=df, keyword="直通", interval=60)
    else:
        pass_num = curd.count_df(df=df, keyword="商检出口", interval=60)
    # 以下获得"产量"的数量，时间间隔是60分钟
    output_num = curd.count_df(df=df, keyword="产量", interval=60)
    # 以下是计算急停(累计)的时间
    sql_pause = "select * from analyse_pause where StartTime between '{0}' AND '{1}' AND StartLine Like '{2}' AND isActive = 1;".format(
        start, end, line)
    df_pause = pd.read_sql_query(sql_pause, engine)
    print("急停的分析数据是",df_pause)
    top5_total = curd.get_pause(df=df_pause)
    # 以下是计算20分钟急停的时间
    start,end = begin_by(interval=20)
    sql_pause = "select * from analyse_pause where StartTime between '{0}' AND '{1}' AND StartLine Like '{2}' AND isActive = 1;".format(
        start, end, line)
    df_pause = pd.read_sql_query(sql_pause, engine)
    print("急停的分析数据是",df_pause)
    top5 = curd.get_pause(df=df_pause)
    return time_list, beat, long_time, pass_num.to_list(), output_num.to_list(),top5_total.to_dict(),top5.to_dict()


def begin_by(interval=None):
    end_time = datetime.datetime.now()
    if interval is None:
        # 20点及以后，选当天的20点作为开始时间
        if end_time.hour >= 20:
            start_time = datetime.datetime(
                end_time.year, end_time.month, end_time.day, 20, 0, 0)
        # 8点前，选前一天的20点作为开始时间
        elif end_time.hour < 8:
            start_time = datetime.datetime(
                end_time.year, end_time.month, end_time.day - 1, 20, 0, 0)
        else:
            start_time = datetime.datetime(
                end_time.year, end_time.month, end_time.day, 8, 0, 0)
    else:
        start_time = end_time - datetime.timedelta(minutes=interval)
    start = start_time.strftime("%Y-%m-%d %H:%M:%S")
    end = end_time.strftime("%Y-%m-%d %H:%M:%S")
    return start, end


def get_time_list():
    time_dict = {
        "day": ['8:00', '9:00', '10:00', '11:00', '12:00', '13:00', '14:00', '15:00', '16:00', '17:00', '18:00',
                '19:00'],
        "night": ['20:00', '21:00', '22:00', '23:00', '0:00', '1:00', '2:00', '3:00', '4:00', '5:00', '6:00', '7:00']
    }
    end_time = datetime.datetime.now()
    if end_time.hour >= 20:
        time_list = time_dict['night']
    elif end_time.hour < 8:
        time_list = time_dict['night']
    else:
        time_list = time_dict['day']
    return time_list


if __name__ == '__main__':
    import uvicorn

    # uvicorn.run(app, host="172.16.0.8", port=80)
    uvicorn.run(app, host="127.0.0.1", port=8080)
